create external table jms_ods.appeal_info (
  id                                bigint        COMMENT 'ID',
  appeal_no                         string        COMMENT '申诉编号',
  waybill_no                        string        COMMENT '运单号',
  user_name                         string        COMMENT '申诉人姓名',
  phone                             string        COMMENT '申诉人手机号码',
  email                             string        COMMENT 'email',
  status                            tinyint       COMMENT '处理状态 1:未受理 2:已受理 3:已答复 4:已拒绝 5:已结案 6:待分配 7:待审核',
  type                              tinyint       COMMENT '申诉种类: 1快递服务、2函件、3包件、4汇兑、5报刊、6集邮、7其他',
  cause                             tinyint       COMMENT '申诉原因: 1.快件延误；2.快件丢失短少；3.快件损毁；4.收寄服务；5.投递服务；6.售后服务；7.违规收费8.其他9.代收货款',
  appeal_time                       timestamp     COMMENT '申诉时间',
  province_id                       bigint        COMMENT '申诉省id',
  province_desc                     string        COMMENT '申诉省',
  city_id                           bigint        COMMENT '申诉市id',
  city_desc                         string        COMMENT '申诉市',
  description                       string        COMMENT '申诉说明',
  trace                             string        COMMENT '流转信息jsonArray',
  evidence                          string        COMMENT '申诉证据信息jsonArray',
  is_insured                        tinyint       COMMENT '是否保价1：是 2：否',
  insured_value                     double        COMMENT '保价金额',
  acceptance_office_province_id     bigint        COMMENT '受理局省id(转办)',
  acceptance_office_province_desc   string        COMMENT '受理局省',
  acceptance_office_city_id         bigint        COMMENT '受理局市id(转办)',
  acceptance_office_city_desc       string        COMMENT '受理局市',
  acceptance_office_time            timestamp     COMMENT '受理局受理时间',
  valid                             tinyint       COMMENT '是否有效（1：有效 2：无效）',
  economic_loss                     double        COMMENT '挽回经济损失',
  discontact_count                  smallint      COMMENT '未联系到用户次数',
  complaints_result                 tinyint       COMMENT '投诉结果1：投诉无果2：对投诉结果不满意',
  remark                            string        COMMENT '备注信息',
  complain_info                     string        COMMENT '投诉结果补充',
  express_time                      timestamp     COMMENT '邮寄时间',
  close_time                        timestamp     COMMENT '结案时间',
  close_office_province_id          bigint        COMMENT '结案省id',
  close_office_province_desc        string        COMMENT '结案省',
  close_office_city_id              bigint        COMMENT '结案市id',
  close_office_city_desc            string        COMMENT '结案市',
  blame_province_id                 bigint        COMMENT '责任省id',
  blame_province_desc               string        COMMENT '责任省',
  blame_city_id                     bigint        COMMENT '责任市id',
  blame_city_desc                   string        COMMENT '责任市',
  sender_name                       string        COMMENT '寄件人姓名',
  sender_phone                      string        COMMENT '寄件人手机号',
  sender_address                    string        COMMENT '寄件人地址',
  send_network_id                   bigint        COMMENT '发件网点id',
  send_network_code                 string        COMMENT '发件网点code',
  send_network_name                 string        COMMENT '发件网点name',
  receiver_name                     string        COMMENT '收件人姓名',
  receiver_phone                    string        COMMENT '收件人手机号',
  receiver_address                  string        COMMENT '收件人地址',
  dispatch_network_id               bigint        COMMENT '派件网点id',
  dispatch_network_code             string        COMMENT '派件网点code',
  dispatch_network_name             string        COMMENT '派件网点name',
  trans_count                       smallint      COMMENT '转办次数',
  trans_reason                      tinyint       COMMENT '转办企业类型 1 正常转办 2企业答复不规范转办',
  trans_time                        timestamp     COMMENT '转办时间',
  accept_network_id                 bigint        COMMENT '受理网点id',
  accept_network_code               string        COMMENT '受理网点code',
  accept_network_name               string        COMMENT '受理网点name',
  accept_time                       timestamp     COMMENT '受理网点-受理时间',
  assign_time                       timestamp     COMMENT '最后分配时间',
  assign_type                       tinyint       COMMENT '分配类型 1:分配网点 2分配客服',
  accept_user_id                    bigint        COMMENT '受理人id',
  accept_user_code                  string        COMMENT '受理人编码',
  accept_user_name                  string        COMMENT '受理人名称',
  blame_network_id                  bigint        COMMENT '责任网点id',
  blame_network_code                string        COMMENT '责任网点code',
  blame_network_name                string        COMMENT '责任网点name',
  blame_proxy_id                    bigint        COMMENT '责任代理区网点id',
  blame_proxy_code                  string        COMMENT '责任代理区网点code',
  blame_proxy_name                  string        COMMENT '责任代理区网点name',
  is_upgrade                        tinyint       COMMENT '是否升级（1：是 2：否）',
  upgrade_network_id                bigint        COMMENT '升级处理网点id',
  upgrade_network_code              string        COMMENT '升级处理网点code',
  upgrade_network_name              string        COMMENT '升级处理网点name',
  upgrade_time                      timestamp     COMMENT '升级时间',
  process_user_id                   bigint        COMMENT '最后处理人id',
  process_user_code                 string        COMMENT '最后处理人code',
  process_user_name                 string        COMMENT '最后处理人name',
  process_time                      timestamp     COMMENT '最后处理时间',
  create_time                       timestamp     COMMENT '创建时间',
  update_time                       timestamp     COMMENT '更新时间',
  audit_opinion                     string        COMMENT '审核意见',
  audit_user_id                     bigint        COMMENT '审核人ID',
  audit_user_code                   string        COMMENT '审核人编码',
  audit_user_name                   string        COMMENT '审核人姓名',
  accept_network_type               tinyint       COMMENT '网点标识,1网点,2转运中心,3集散点 4:其他',
  accept_proxy_id                   bigint        COMMENT '受理网点所属代理区ID',
  accept_proxy_code                 string        COMMENT '受理网点所属代理区编码',
  accept_proxy_name                 string        COMMENT '受理网点所属代理区名称',
  accept_franchisee_id              bigint        COMMENT '受理网点所属加盟商ID',
  accept_franchisee_code            string        COMMENT '受理网点所属加盟商编码',
  accept_franchisee_name            string        COMMENT '受理网点所属加盟商名称',
  monitoring_time                   timestamp     COMMENT '监控报表时间',
  process_duration                  bigint        COMMENT '处理时长',
  response_duration                 bigint        COMMENT '响应时长',
  is_monitor_history                tinyint       COMMENT '是否是监控报表的历史数据 1是 2否',
  is_repeat                         tinyint       COMMENT '是否重复(1:不重复,2:重复)'
) COMMENT '申诉单表'
 PARTITIONED BY (
   `dt` string COMMENT '时间分区(yyyy-MM-dd)')
stored as parquet
 LOCATION
   '/dw/hive/jms_ods.db/external/appeal_info'
     TBLPROPERTIES (
        'parquet.column.index.access' = 'true'
        );

alter table jms_ods.appeal_info add columns (
receiver_province_name string COMMENT '收件省份名称',
receiver_province_id bigint COMMENT '收件省份id',
receiver_city_name string COMMENT '收件城市名称',
receiver_city_id bigint COMMENT '收件城市id',
receiver_area_name string COMMENT '收件区域名称',
receiver_area_id bigint COMMENT '收件区域id'
    ) cascade
;

-- 2024-01-05 添加字段 kumiler
alter table jms_ods.appeal_info add columns (
product_type string COMMENT '产品类型',
is_overdue int COMMENT '未逾期 1：是 2：否',
is_closed int COMMENT '一次结案 1：是 2：否',
is_satisfied int COMMENT '是否满意 1：是 2：否',
is_standard_reply int COMMENT '答复规范 1：是 2：否',
is_true_reply int COMMENT '答复属实 1：是 2：否'
    ) cascade ;
